How to create user and grant permission in SQL server?

Question

Ashutosh Kumar Verma · Answer

SQL Server Create User and Grant Permission
Once a user is created using the CREATE USER statement, the user has no permissions on database objects such as
tables, views, and indexes.
Create New User
For creating new users in the database, first, we created a login testLogin
with password,
CREATE LOGIN testLogin
WITH PASSWORD='UyxIv@12'
Note- use master database for creating any login in the SQL database.
Now, move to the current database to MyCollegeDb and create a user for
testLogin login.
USE MyCollegeDb
GO
CREATE USER varans
FOR LOGIN testLogin;
When we create a new user in the database he doesn't have any permissions on the database objects like tables, views, and indexes.
To allow a user to interact with database objects, you must grant permissions to the user. For example, you can grant permissions so that a user can select data from a table.
and 
SQL Server GRANT statement
To grant permission to a user, you use the SQL GRANT statement.
The GRANT statement allows you to grant permission on the 
securable to the principal.
A securable is a resource to which the SQL Server authorization system controls access. For example, a table is a securable.A principal is an entity that can request a SQL Server resource. For example, a user is a principal in SQL Server.
Syntax-
GRANT permissions 
ON securable TO principal;
Explanation-
First, specify one or more permissions after the GRANT keyword. If you have multiple permissions, you must use a comma to separate the permissions.
Second, specify a securable after the ON keyword.
Third, specify a principal after the TO keyword.
Example-
Let's create a table People in the database MyCollegeDb and inserts some values into it,
USE MyCollegeDb
GO
CREATE TABLE People (
 ID INT IDENTITY PRIMARY KEY,
 FirstName varchar(50) NOT NULL,
 LastName varchar(50) NOT NULL
);
INSERT INTO People (FirstName, LastName)
 VALUES ('John', 'Doe'),
 ('Jacob', 'Mark'),
 ('John', 'Smith'),
 ('Dach', 'Keon');
If you connect to SQL Server using login testLogin. You will see that user
varans can access the MyCollegeDb database but can't see any tables. See in the below picture,
and 
Grant the SELECT Permission
Switch to the system administrator connection and grant SELECT permission to the user
varans on the People table.
USE MyCollegeDb
GO
GRANT SELECT 
ON People TO varans;
Now, the user varans can see the People table and select data from it. For example,
SELECT * FROM People;
However, the user varans cannot insert or update data into the 
People table.
Multiple Grant Permission
Grant the INSERT and DELETE permissions on the 
People table to the user varans.
USE MyCollegeDb
GO
GRANT INSERT, DELETE
ON People TO varans;
Now, switch to the user varans connection and insert a new row into the
People table,
INSERT INTO People
VALUES('Maria', 'Jaen');
Now, the user varans can insert data into and delete data from the
People table.
and 
Also, Read: 
How to drop a user in SQL Server?

forum

How to create user and grant permission in SQL server?

Sandra Emily

Can you answer this question?

1 Answers

SQL Server Create User and Grant Permission

Liked By